﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;

namespace EWDT_CDS_Hub.App_Code
{
    public class StudentToClass
    {
        public String Subject_id { get; set; }
        public String Class_id { get; set; }
        public String Student_id { get; set; }

        public static List<StudentToClass> GetStudentToClass()
        {
            List<StudentToClass> results = new List<StudentToClass>();
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM StudentToClass";
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    StudentToClass s = new StudentToClass();
                    s.Student_id = (String)dr["student_id"];
                    s.Subject_id = (String)dr["subject_id"];
                    s.Class_id = (String)dr["class_id"];

                    results.Add(s);
                }
            }
            catch (SqlException e)
            {
            }
            finally
            {
                conn.Close();
            }

            return results;
        }

        public static int InsertStudentToClass(StudentToClass s)
        {
            int rowsInserted = 0;
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO StudentToClass (subject_id,class_id, student_id) VALUES (@sbjid, @classid, @studid)";
                comm.Parameters.AddWithValue("@sbjid", s.Subject_id);
                comm.Parameters.AddWithValue("@classid", s.Class_id);
                comm.Parameters.AddWithValue("@studid", s.Student_id);
                rowsInserted = comm.ExecuteNonQuery();

            }
            catch (SqlException ex)
            {

            }
            finally
            {
                conn.Close();
            }

            return rowsInserted;
        }

        //retrieve list of students in a class
        public static List<Student> RetrieveClassOfStudent(String class_id)
        {
            List<Student> results = new List<Student>();
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Student WHERE student_id IN (SELECT student_id FROM StudentToClass WHERE class_id = @id)";
                comm.Parameters.AddWithValue("@id", class_id);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Student s = new Student();
                    s.Student_id = (String)dr["student_id"];
                    s.Student_name = (String)dr["student_id"];
                    s.Student_email = (String)dr["student_email"];
                    s.Care_group_id = (String)dr["care_group_id"];
                    results.Add(s);
                }

                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return results;
        }

        //retrieve list of subjects that has at least 1 class
        public static List<Subject> RetrieveAvailableSubjects()
        {
            List<Subject> results = new List<Subject>();
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Subject WHERE subject_id IN (SELECT DISTINCT subject_id FROM StudentToClass)";
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Subject s = new Subject();
                    s.Subject_id = (String)dr["subject_id"];
                    s.Subject_name = (String)dr["subject_name"];
                    s.Subject_desc = (String)dr["subject_desc"];
                    s.Subject_type = (String)dr["subject_type"];
                    results.Add(s);
                }

                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return results;
        }
    }
}